
package keter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.owasp.esapi.ESAPI;
import org.owasp.esapi.codecs.MySQLCodec;
import org.owasp.esapi.codecs.MySQLCodec.Mode;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * <p>
 * 数据库直接操作工具
 * </p>
 * 
 * @author 顾力行 - gulx@neusoft.com
 * @version 1.0 Created on Apr 15, 2009 6:48:04 PM
 */
public class DBUtil {
    /**
     * Logger for this class
     */
    private static final Logger logger = LoggerFactory.getLogger(DBUtil.class);
    private static Connection conn;
    private static Statement stmt;
    private static PreparedStatement pstmt;
    private static ResultSet rstr;
    private static String strConnectionURL = null;
    private static String username = null;
    private static String passwd = null;

    /**
     * 配置文件初始化：只做一次！
     */
    static {
//      log.info(" init datasource context...");
//      strConnectionURL = "jdbc:hsqldb:hsql://localhost";
    };
    
    public static void init(String url,String username, String passwd){
        logger.info(" init datasource context...");
        strConnectionURL = url;
        DBUtil.username = username;
        DBUtil.passwd = passwd;
    }

    /**
     * <p>
     * 执行查询
     * </p>
     * 
     * @param strConnectionURL
     * @param userNamePwd
     * @param sql
     * @param type
     * @return
     * @author: 顾力行 - gulx@neusoft.com
     * @throws SQLException 
     * @date: Created on Apr 27, 2009 10:44:07 AM
     */
    public static ResultSet executeQuery(String sql) {
        prepareDBDriver();
        try {
            prepareQuery(sql);
        } catch (SQLException e) {
            logger.error("error:",e);
            throw new RuntimeException("bad sql:["+sql+"]");
        }
        return rstr;
    }
    
    public static ResultSet executeQuery(String sql,String... params) {
        prepareDBDriver();
        try {
            prepareParamQuery(sql,params);
        } catch (SQLException e) {
            logger.error("error:",e);
            throw new RuntimeException("bad sql:["+sql+"]");
            
        }
        return rstr;
    }

    public static void executeUpdate(String sql) throws Exception {
        prepareDBDriver();
        prepareUpdate(sql);
    }

    /**
     * <p>
     * Method ：prepare
     * <p>
     * Description : 构建数据库驱动
     * 
     * @author 顾力行-gulx@neusoft.com
     *         <p>
     *         --------------------------------------------------------------<br>
     *         修改履历：<br>
     *         <li>2010-8-30，gulx@neusoft.com，创建方法；<br>
     *         --------------------------------------------------------------
     *         <br>
     *         </p>
     */
    private static void prepareDBDriver() {
        try {
            if(strConnectionURL.contains("oracle")){
                Class.forName("oracle.jdbc.driver.OracleDriver");
            }
            else if(strConnectionURL.contains("hsqldb")){
                Class.forName("org.hsqldb.jdbcDriver");
            }
            else if(strConnectionURL.contains("mysql")){
                Class.forName("com.mysql.jdbc.Driver");
            }
        } catch (ClassNotFoundException e) {
            logger.error("error:",e);
            e.printStackTrace();
        }
    }

    /**
     * <p>
     * 结果集准备
     * </p>
     * 
     * @param strConnectionURL
     * @param userNamePwd
     * @param sql
     * @throws SQLException
     * @author: 顾力行 - gulx@neusoft.com
     * @date: Created on Apr 20, 2009 4:55:34 PM
     */
    private static void prepareQuery(String sql) throws SQLException {
            conn = DriverManager.getConnection(strConnectionURL, username, passwd);
            stmt = conn.createStatement();
            rstr = stmt.executeQuery(sql);
    }
    
    private static void prepareParamQuery(String sql,String... params) throws SQLException {
        conn = DriverManager.getConnection(strConnectionURL, username, passwd);
        pstmt = conn.prepareStatement(sql);
        int i=1;
        for (String param : params) {
            pstmt.setString(i, param);
            i++;
        }       
        rstr = pstmt.executeQuery();
    }

    /**
     * <p>
     * 结果集准备
     * </p>
     * 
     * @param strConnectionURL
     * @param userNamePwd
     * @param sql
     * @throws SQLException
     * @author: 顾力行 - gulx@neusoft.com
     * @throws Exception 
     * @date: Created on Apr 20, 2009 4:55:34 PM
     */
    private static void prepareUpdate(String sql) throws Exception {
        try {
            conn = DriverManager.getConnection(strConnectionURL, username, passwd);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new Exception(e);
        }
    }

    /**
     * <p>
     * 关闭连接 需要在结果集使用完成之后操作
     * </p>
     * 
     * @author: 顾力行 - gulx@neusoft.com
     * @date: Created on Apr 20, 2009 4:57:13 PM
     */
    public static void close() {
        if (conn != null && stmt != null) {
            try {
                conn.close();
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String args[]) throws SQLException {
//        Properties p = new PropertiesLoader("classpath:/database.properties").getProperties();  
//        System.out.println(p.getProperty("jdbc.url"));
        String sql = "";
        /* mysql test*/
      DBUtil.init("jdbc:mysql://192.168.241.96:3306/paper?useUnicode=true&characterEncoding=utf-8","root","mysql");
      String p = "root";
      p = "root 'or '1=1";
      p = ESAPI.encoder().encodeForSQL(new MySQLCodec(Mode.STANDARD), p);
      sql = "select count(1) from mysql.user where user='"+p+"' and host = '127.0.0.1';";      
      logger.info(sql);
      ResultSet rstr = DBUtil.executeQuery(sql);
      while (rstr.next()) {
          String USR_HOST = rstr.getString(1);
          System.out.println("round1: " + USR_HOST);
      }      
      sql = "select count(1) from mysql.user where user=?";
      rstr = DBUtil.executeQuery(sql,"root");
      while (rstr.next()) {
          String USR_HOST = rstr.getString(1);
          System.out.println("round1: " + USR_HOST);
      }
      DBUtil.close();
        
    }
}